package com.xbongbong.pro.bi.commons.filter;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.xbongbong.paas.constant.TimeConstant;
import com.xbongbong.paas.constant.XbbConstant;
import com.xbongbong.paas.domain.entity.PaasFormRefEntity;
import com.xbongbong.paas.domain.entity.ext.ConditionsEntityExt;
import com.xbongbong.paas.elasticsearch.util.EsUtil;
import com.xbongbong.paas.enums.AddressEnum;
import com.xbongbong.paas.enums.BIProductReformEnum;
import com.xbongbong.paas.enums.BiEsAliasEnum;
import com.xbongbong.paas.enums.ConditionEnum;
import com.xbongbong.paas.enums.EsAliasEnum;
import com.xbongbong.paas.enums.FieldTypeEnum;
import com.xbongbong.paas.enums.FlowStatusEnum;
import com.xbongbong.paas.enums.bi.XbbTableRefEnum;
import com.xbongbong.paas.model.PaasFormRefModel;
import com.xbongbong.paas.toolbox.exception.XbbException;
import com.xbongbong.paas.toolbox.util.CollectionsUtil;
import com.xbongbong.paas.toolbox.util.DateTimeUtil;
import com.xbongbong.paas.toolbox.util.RegexUtil;
import com.xbongbong.paas.toolbox.util.StringUtil;
import com.xbongbong.pro.statistics.help.CommonHelper;
import com.xbongbong.pro.bi.commons.helper.StringHelper;
import com.xbongbong.pro.bi.commons.sql.SqlBuilderHandler;
import com.xbongbong.pro.constant.BusinessConstant;
import com.xbongbong.pro.constant.SymbolConstant;
import com.xbongbong.pro.service.toolbox.statistic.help.BIHelp;
import com.xbongbong.saas.constant.BasicConstant;
import com.xbongbong.saas.constant.ParameterConstant;
import com.xbongbong.saas.constant.StringConstant;
import com.xbongbong.saas.enums.SheetAloneEnum;
import com.xbongbong.saas.enums.WorkOrderTypeEnum;
import com.xbongbong.saas.enums.XbbRefTypeEnum;
import com.xbongbong.saas.enums.business.ContractEnum;
import com.xbongbong.saas.enums.business.ContractPerformanceEnum;
import com.xbongbong.saas.enums.business.CustomerCommunicateEnum;
import com.xbongbong.saas.enums.business.ProductEnum;
import org.apache.commons.lang3.StringUtils;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import static com.xbongbong.paas.constant.XbbConstant.JSON_EXTRACT;
import static com.xbongbong.paas.constant.XbbConstant.JSON_EXTRACT_ARRAY_RAW;
import static com.xbongbong.paas.constant.XbbConstant.JSON_EXTRACT_RAW;
import static com.xbongbong.paas.constant.XbbConstant.JSON_EXTRACT_STRING;
import static com.xbongbong.paas.constant.XbbConstant.STRING_CONSTANT;
import static com.xbongbong.paas.elasticsearch.constant.EsNormalConstant.MINI_MUM_SHOULD_MATCH;
import static com.xbongbong.pro.statistics.help.CommonHelper.getTeamNameByBusinessType;
import static org.elasticsearch.index.query.QueryBuilders.boolQuery;

/**
 * @author gzt
 * @version 1.0
 * @Description -
 * @datetime - 2022/6/1-15:06
 * @since v1.0
 */
@Component
public class  FilterHandler{
    @Resource
    private PaasFormRefModel paasFormRefModel;
    public static String currentSubFormName = null;

    private static HashMap<Integer, String> subFormHashMap = Maps.newHashMap();
    private static HashMap<Integer, String> teamUserHashMap = Maps.newHashMap();



    public void parseFilterOrSearchCondition(String corpid, SqlBuilderHandler sqlBuilder, JSONArray conditionJson, boolean isSingle, boolean isFilterCondition,
                                             Map<Long, Boolean> sourcesMap, boolean isPublicFlag, Integer businessType) throws XbbException {
        if (conditionJson.size() > 0) {
            HashMap<String, HashSet<String>> subFormMap = Maps.newHashMap();
            conditionJson = getSortArrayJson(conditionJson);
            for (Object o : conditionJson) {
                JSONObject filterJson = (JSONObject) o;
                if (filterJson.size() ==0 ){return;}
                ConditionsEntityExt condition = JSONObject.parseObject(filterJson.toJSONString(), ConditionsEntityExt.class);
                String parentAttr = filterJson.getString("parentAttr");
                if (parentAttr != null && !Objects.equals(parentAttr, "")) {
                    JSONObject parentObj = filterJson.getJSONObject("parentSubFormAttr");
                    Integer parentFieldType = parentObj == null ? null : parentObj.getInteger("fieldType");
                    if (!Objects.equals(parentFieldType, FieldTypeEnum.CONTACT_NUMBER.getType())) {
                        condition.setSubAttr(parentAttr);
                    }
                }
                boolean emptyFlag = Objects.equals(condition.getSymbol(), ConditionEnum.EMPTY.getSymbol()) || Objects.equals(condition.getSymbol(), ConditionEnum.NOEMPTY.getSymbol());
                //如果value值为空则跳过
                if (!emptyFlag && (Objects.isNull(condition.getValue()) || condition.getValue().isEmpty())) {
                    continue;
                }
                if (!emptyFlag && (Objects.isNull(condition.getValue()) || Objects.isNull(condition.getValue().get(0)) || "".equals(condition.getValue().get(0)))) {
                    continue;
                }
                businessType = filterJson.getInteger("businessType");
                boolean isNeedRelationSubForm = isNeedRelationSubFormTable(businessType, parentAttr);
                if (isNeedRelationSubForm) {
                    getSubFormAttrCountMap(subFormMap, filterJson, parentAttr);
                }
                //Long formId = filterJson.getLong("formId");
                Long formId = condition.getFormId();
                if (formId == null) {
                    return;
                }
                boolean isTeam=false;
                if (Objects.isNull(sourcesMap)){
                    //sourcesMap.put(formId, isTeamLogic);
                    isTeam = BIHelp.isTeamLogicByBusinessType(businessType);
                }else{
                    isTeam = sourcesMap.get(formId.longValue());
                }
                ConditionEnum conditionEnum = ConditionEnum.getConditionEnum(condition.getSymbol());
                conditionSymbolChoose(corpid, sqlBuilder, conditionEnum, condition, isTeam, isPublicFlag, isSingle, businessType);
            }
            subFormMap.forEach((k, v) -> {
                if (v.size() > 0) {
                    v.stream().forEach(a -> sqlBuilder.append(")"));
                    /*StringBuffer stringBuffer = fliterMap.get(k);
                    v.stream().forEach(a -> stringBuffer.append(")"));*/
                }
            });
        }

    }

   /* public void parseFilterOrSearchCondition(String corpid, SqlBuilderHandler sqlBuilder, JSONArray conditionJson, boolean isSingle, boolean isFilterCondition,
                                             Map<Integer, Boolean> sourcesMap, boolean isPublicFlag) throws XbbException {
        if (conditionJson.size() > 0) {
            HashMap<String, HashSet<String>> subFormMap = Maps.newHashMap();
            conditionJson = getSortArrayJson(conditionJson);
            for (Object o : conditionJson) {
                JSONObject filterJson = (JSONObject) o;
                ConditionsEntityExt condition = JSONObject.parseObject(filterJson.toJSONString(), ConditionsEntityExt.class);
                String parentAttr = filterJson.getString("parentAttr");
                if (parentAttr != null && !Objects.equals(parentAttr, "")) {
                    JSONObject parentObj = filterJson.getJSONObject("parentSubFormAttr");
                    Integer parentFieldType = parentObj == null ? null : parentObj.getInteger("fieldType");
                    if (!Objects.equals(parentFieldType, FieldTypeEnum.CONTACT_NUMBER.getType())) {
                        condition.setSubAttr(parentAttr);
                    }
                }
                boolean emptyFlag = Objects.equals(condition.getSymbol(), ConditionEnum.EMPTY.getSymbol()) || Objects.equals(condition.getSymbol(), ConditionEnum.NOEMPTY.getSymbol());
                //如果value值为空则跳过
                if (!emptyFlag && (Objects.isNull(condition.getValue()) || condition.getValue().isEmpty())) {
                    continue;
                }
                if (!emptyFlag && (Objects.isNull(condition.getValue()) || Objects.isNull(condition.getValue().get(0)) || "".equals(condition.getValue().get(0)))) {
                    continue;
                }
                boolean isNeedRelationSubForm = isNeedRelationSubFormTable(filterJson.getInteger("businessType"), parentAttr);
                if (isNeedRelationSubForm) {
                    getSubFormAttrCountMap(subFormMap, filterJson, parentAttr);
                }
                Long formId = filterJson.getLong("formId");
                boolean isTeam = sourcesMap.get(formId.intValue());
                ConditionEnum conditionEnum = ConditionEnum.getConditionEnum(condition.getSymbol());
                conditionSymbolChoose(corpid,sqlBuilder , conditionEnum, condition, isTeam, isPublicFlag, isSingle, filterJson.getInteger("businessType"));
            }
            subFormMap.forEach((k, v) -> {
                if (v.size() > 0) {
                    *//*StringBuffer stringBuffer = fliterMap.get(k);
                    v.stream().forEach(a -> stringBuffer.append(")"));*//*
                }
            });
        }

    }*/

    /**
     * @param subFormMap
     * @param filterJson
     * @param parentAttr
     * @return void
     * @throws
     * @Description 获得主表、从表子表单筛选去重后数量，用不空值括号添加
     * @Date 21:52 2022/1/12
     **/

    private void getSubFormAttrCountMap(HashMap<String, HashSet<String>> subFormMap, JSONObject filterJson, String parentAttr) {
        if (!Objects.isNull(filterJson) && StringUtils.isNotEmpty(parentAttr)) {
            String formId = filterJson.getLong("formId").toString();
            if (StringUtils.isNotEmpty(formId)) {
                HashSet<String> subFormSet = subFormMap.get(formId);
                if (Objects.isNull(subFormSet)) {
                    subFormSet = Sets.newHashSet();
                    subFormSet.add(parentAttr);
                    subFormMap.put(formId, subFormSet);
                } else if (!subFormSet.contains(parentAttr)) {
                    subFormSet.add(parentAttr);
                    subFormMap.put(formId, subFormSet);
                }
            }
        }
    }


    /**
     * @param conditionJson
     * @return com.alibaba.fastjson.JSONArray
     * @Description 获取排序后的JsonArray
     * @Date 16:53 2022/1/10
     **/

    private JSONArray getSortArrayJson(JSONArray conditionJson) {
        JSONArray jsonArray = null;
        if (conditionJson != null) {
            List<JSONObject> list = JSONArray.parseArray(conditionJson.toJSONString(), JSONObject.class);
            Collections.sort(list, new Comparator<JSONObject>() {
                @Override
                public int compare(JSONObject o1, JSONObject o2) {
                    String a = o1.getString("parentAttr");
                    String b = o2.getString("parentAttr");
                    if (a == null) {
                        a = "a";
                    }
                    if (b == null) {
                        b = "a";
                    }
                    return a.compareTo(b);
                }
            });
            jsonArray = JSONArray.parseArray(list.toString());
        }
        return jsonArray;
    }

    /**
     * 自定义图表条件语句组装(筛选,穿透)
     *
     * @param conditionEnum 筛选类型
     * @param condition     筛选详情
     * @param businessType  业务code
     */
    private void conditionSymbolChoose(String corpid, SqlBuilderHandler sqlBuilder, ConditionEnum conditionEnum,
                                       ConditionsEntityExt condition, boolean isTeam, boolean isPublicFlag, boolean isSingle, Integer businessType) {
        subFormParseValue(condition);
        //subAttr不为空为子表单字段 且单表情况下
        if (Objects.nonNull(condition.getSubAttr()) && isSingle) {
            subFormConditionSymbolChoose(corpid, sqlBuilder, conditionEnum, condition, isSingle, businessType);
        } else if (FieldTypeEnum.OWNERID.getType().equals(condition.getFieldType()) && isTeam) {
            teamConditionSymbolChoose(sqlBuilder, conditionEnum, condition, isTeam, 1, isPublicFlag, isSingle, businessType);
        } else if (FieldTypeEnum.COUSERID.getType().equals(condition.getFieldType()) && isTeam) {
            teamConditionSymbolChoose(sqlBuilder, conditionEnum, condition, isTeam, 0, false, isSingle, businessType);
        } else if (FieldTypeEnum.BIRTHDAY.getType().equals(condition.getFieldType())) {
//            conditionBirthday(boolQueryBuilder, (int) condition.getValue().get(0), null);
        } else if (FieldTypeEnum.ADDRESS.getType().equals(condition.getFieldType())) {
            biAddressConditionSymbolChoose(corpid, sqlBuilder, conditionEnum, condition, isSingle);
        } else {
            commonConditionSymbolChoose(corpid, sqlBuilder, conditionEnum, condition, isSingle, businessType);
        }
    }

    /**
     * 团队筛选解析
     *
     * @param sqlBuilder     查询语句载体
     * @param conditionEnum 筛选类型
     * @param condition     筛选详情
     * @param isTeam        判断该表单是否走团队逻辑
     * @param isMain        判断是负责人或协同人
     * @param isPublicFlag  是否是公海客户 del = -1
     * @param businessType  业务code
     * @author zhouwq
     * @date 2019/5/7 15:59
     */
    private static void teamConditionSymbolChoose(SqlBuilderHandler sqlBuilder, ConditionEnum conditionEnum, ConditionsEntityExt condition, boolean isTeam,
                                                  Integer isMain, boolean isPublicFlag, boolean isSingle, Integer businessType) {
        //转到对应的团队下
        String teamName = getTeamNameByBusinessType(condition.getBusinessType());
        Long formId = condition.getFormId();
        /*StringBuffer stringBuffer = fliterMap.get(formId + "_" + teamName);
        if (Objects.isNull(stringBuffer)) {
            stringBuffer = new StringBuffer();
            fliterMap.put(formId + "_" + teamName, stringBuffer);
        }*/
        List<Object> values = condition.getValue();
        List<String> userIdList = new ArrayList<>();
        for (int i = 0; i < values.size(); i++) {
            String obj = values.get(i).toString();
            //穿透是字符串有逗号拼装，筛选是数组
            if (obj.contains(",")) {
                String[] array = obj.split(",");
                userIdList.addAll(Arrays.asList(array));
            } else {
                userIdList.add(obj);
            }
        }
        switch (conditionEnum) {
            case EQUAL:
            case ALLINCLUDE:
                if (isTeam) {
                    StringBuilder valuesString = new StringBuilder();
                    valuesString.append("[");
                    for (int i = 0; i < userIdList.size(); i++) {
                        if (i > 0) {
                            valuesString.append(",");
                        }
                        valuesString.append("'").append(userIdList.get(i)).append("'");
                    }
                    valuesString.append("]");
                    String team = XbbConstant.TEAM_MAIN;
                    if (isMain == 0) {
                        team = XbbConstant.TEAM_CO;
                    }
                    sqlBuilder.append(XbbConstant.AND).append("hasAll(" + team + "," + valuesString + ")").append(XbbConstant.SPACE);
                    sqlBuilder.append(XbbConstant.AND).append(XbbConstant.LENGTH + "(" + valuesString + ")").append(" = ").append(XbbConstant.LENGTH).append("(" + team + " )");
                }
                break;
            case IN:
            case INCLUDE:
                if (isTeam) {
                    StringBuilder valuesString = new StringBuilder();
                    valuesString.append("(");
                    for (int i = 0; i < userIdList.size(); i++) {
                        if (i > 0) {
                            valuesString.append(" or ");
                        }
                        String team = XbbConstant.TEAM_MAIN;
                        if (isMain == 0) {
                            team = XbbConstant.TEAM_CO;
                        }
                        valuesString.append("has(" + team + ",'" + userIdList.get(i) + "')");
                    }
                    valuesString.append(")");
                    sqlBuilder.append(XbbConstant.AND).append(valuesString.toString()).append(XbbConstant.SPACE);
                }
                break;
            case NOEQUAL:
            case NOIN:
                break;
            case EMPTY:
                //TODO 为空不为空等确定方案
                break;
            case NOEMPTY:
                break;
            default:
                break;
        }
    }

    /**
     * @return void
     * @throws
     * @Description
     * @Date 9:49 2021/12/30
     * @Param [corpid, fliterMap, conditionEnum, condition, isSingle]
     **/

    private void subFormConditionSymbolChoose(String corpid, SqlBuilderHandler sqlBuilder, ConditionEnum conditionEnum,
                                              ConditionsEntityExt condition, boolean isSingle, Integer businessType) {
        commonConditionSymbolChoose(corpid, sqlBuilder, conditionEnum, condition, isSingle, businessType);
    }

    private void biAddressConditionSymbolChoose(String corpid, SqlBuilderHandler sqlBuilder, ConditionEnum conditionEnum, ConditionsEntityExt condition, boolean isSingle) {
        String attr = condition.getAttr();
        if (!isSingle) {
            attr = Objects.isNull(condition.getOriginalAttr()) ? attr : condition.getOriginalAttr();
        }
        List<Object> values = condition.getValue();
        Long formId = condition.getFormId();
        //StringBuffer stringBuffer = fliterMap.get(formId.toString());
        //生成初始的sql拼装语句
        //stringBuffer = initSqlString(corpid, formId, condition.getBusinessType(), fliterMap, stringBuffer, attr);
        sqlBuilder = initSqlString(corpid, formId, condition.getBusinessType(), sqlBuilder, attr);
        String province = null;
        String city = null;
        String district = null;
        if (values != null) {
            province = values.size() > 0 ? values.get(0).toString() : null;
            city = values.size() > 1 ? values.get(1).toString() : null;
            district = values.size() > 2 ? values.get(2).toString() : null;
        }
        boolean containsAddress = attr.contains(BusinessConstant.PROVINCE) || attr.contains(BusinessConstant.CITY) || attr.contains(BusinessConstant.DISTRICT);
        switch (conditionEnum) {
            case EQUAL:
                // and  JSONExtractString(JSONExtractRaw(data,'address_1'),'city') ='杭州市'
                addressSql(attr, sqlBuilder, province, city, district, " = ", XbbConstant.AND, containsAddress);
                break;
            case NOEQUAL:
                //TODO   根据等于修改
                addressSql(attr, sqlBuilder, province, city, district, " != ", XbbConstant.AND, containsAddress);
                break;
            case EMPTY:
      /*          BoolQueryBuilder boolQuery = boolQuery();
                //兼容穿透查询
                if (Objects.equals(condition.getPenetrateFlag(), BasicConstant.ONE) || containsAddress) {
                    boolQuery.should(boolQuery().filter(termQuery(attr + keyword, "")));
                } else {
                    boolQuery.should(boolQuery().filter(termQuery(attr + StringConstant.POINT + BusinessConstant.PROVINCE + keyword, "")));
                }
                boolQuery.minimumShouldMatch(MINI_MUM_SHOULD_MATCH);*/
//                boolQueryBuilder.must(boolQuery);
                sqlBuilder.append(XbbConstant.AND).append(XbbConstant.SPACE).append(XbbConstant.L_P);
                //address_为空
                //PROVINCE为空
                String addressKey = attr;
                String provinceKey = BusinessConstant.PROVINCE;
                if (containsAddress) {
                    String[] attrArray = attr.split("\\.");
                    addressKey = attrArray[0];
                    provinceKey = attrArray[1];
                }
                sqlBuilder.append(XbbConstant.SPACE)
                        .append(JSON_EXTRACT_STRING).append("(").append(JSON_EXTRACT_RAW).append("(data," + "'").append(addressKey).append("'").append(")").append(",").append("'").append(provinceKey).append("'").append(")").append(" = ")
                        .append("'").append("'");
                sqlBuilder.append(XbbConstant.OR).append(XbbConstant.SPACE)
                        .append(JSON_EXTRACT_STRING).append("(").append(JSON_EXTRACT_RAW).append("(data," + "'").append(addressKey).append("'").append(")").append(",").append("'").append("address").append("'").append(")").append(" = ")
                        .append("'").append("'");
                sqlBuilder.append(XbbConstant.SPACE).append(XbbConstant.R_P);
                break;
            case NOEMPTY:
                //TODO
               /* if (Objects.equals(condition.getPenetrateFlag(), BasicConstant.ONE) || containsAddress) {
                    boolQueryBuilder.filter(boolQuery().mustNot(termQuery(attr + keyword,"")));
                    boolQueryBuilder.filter(boolQuery().must(existsQuery(attr + keyword)));
                } else {
                    boolQueryBuilder.filter(boolQuery().mustNot(termQuery(attr + StringConstant.POINT + BusinessConstant.PROVINCE + keyword,"")));
                    boolQueryBuilder.filter(boolQuery().must(existsQuery(attr + StringConstant.POINT + BusinessConstant.PROVINCE + keyword)));
                }*/
                sqlBuilder.append(XbbConstant.AND).append(XbbConstant.SPACE).append(XbbConstant.L_P);
                //address_不为空
                //PROVINCE不为空
                String addresskey = attr;
                String provincekey = BusinessConstant.PROVINCE;
                if (containsAddress) {
                    String[] attrArray = attr.split("\\.");
                    addresskey = attrArray[0];
                    provincekey = attrArray[1];
                }
                sqlBuilder.append(XbbConstant.SPACE)
                        .append(JSON_EXTRACT_STRING).append("(").append(JSON_EXTRACT_RAW).append("(data," + "'").append(addresskey).append("'").append(")").append(",").append("'").append(provincekey).append("'").append(")").append(" != ")
                        .append("'").append("'");
                sqlBuilder.append(XbbConstant.OR).append(XbbConstant.SPACE)
                        .append(JSON_EXTRACT_STRING).append("(").append(JSON_EXTRACT_RAW).append("(data," + "'").append(addresskey).append("'").append(")").append(",").append("'").append("address").append("'").append(")").append(" != ")
                        .append("'").append("'");
                sqlBuilder.append(XbbConstant.SPACE).append(XbbConstant.R_P);
                break;
            case LIKE:
                BoolQueryBuilder likeBoolQuery = boolQuery();
                if (province != null) {
                    if (Objects.equals(condition.getPenetrateFlag(), BasicConstant.ONE) || containsAddress) {
                        //TODO
//                        likeBoolQuery.should(matchPhraseQuery(attr, province));
                    } else {
                /*        likeBoolQuery.should(matchPhraseQuery(attr + StringConstant.POINT + BusinessConstant.PROVINCE, province));
                        likeBoolQuery.should(matchPhraseQuery(attr + StringConstant.POINT + BusinessConstant.CITY, province));
                        likeBoolQuery.should(matchPhraseQuery(attr + StringConstant.POINT + BusinessConstant.DISTRICT, province));
                        likeBoolQuery.should(matchPhraseQuery(attr + StringConstant.POINT + BusinessConstant.ADDRESS, province));*/
                        addressSql(attr, sqlBuilder, province, province, province, " like ", XbbConstant.OR, containsAddress);
                        sqlBuilder.append(XbbConstant.OR).append(XbbConstant.SPACE)
                                .append(JSON_EXTRACT_STRING).append("(").append(JSON_EXTRACT_RAW).append("(data," + "'").append(attr).append("'").append(")").append(",").append("'").append(BusinessConstant.ADDRESS).append("'").append(")").append(" like ")
                                .append("'").append(province).append("'");
                    }
                }
                likeBoolQuery.minimumShouldMatch(MINI_MUM_SHOULD_MATCH);
//                boolQueryBuilder.filter(likeBoolQuery);
                break;
            case NOLIKE:
                BoolQueryBuilder noLikeBoolQuery = boolQuery();
                if (province != null) {
                    if (Objects.equals(condition.getPenetrateFlag(), BasicConstant.ONE) || containsAddress) {
                        //TODO
//                        noLikeBoolQuery.must(boolQuery().mustNot(matchPhraseQuery(attr, province)));
                    } else {
                        /*noLikeBoolQuery.must(boolQuery().mustNot(matchPhraseQuery(attr + StringConstant.POINT + BusinessConstant.PROVINCE, province)));
                        noLikeBoolQuery.must(boolQuery().mustNot(matchPhraseQuery(attr + StringConstant.POINT + BusinessConstant.CITY, province)));
                        noLikeBoolQuery.must(boolQuery().mustNot(matchPhraseQuery(attr + StringConstant.POINT + BusinessConstant.DISTRICT, province)));*/
//                        noLikeBoolQuery.must(boolQuery().mustNot(matchPhraseQuery(attr + StringConstant.POINT + BusinessConstant.ADDRESS, province)));
                        //不等于前端把值放在了province上
                        addressSql(attr, sqlBuilder, province, province, province, " not like ", XbbConstant.AND, containsAddress);
                        sqlBuilder.append(XbbConstant.AND).append(XbbConstant.SPACE)
                                .append(JSON_EXTRACT_STRING).append("(").append(JSON_EXTRACT_RAW).append("(data," + "'").append(attr).append("'").append(")").append(",").append("'").append(BusinessConstant.ADDRESS).append("'").append(")").append(" not like ")
                                .append("'").append(province).append("'");
                    }
                }
                break;
            default:
                break;
        }
    }

    private static void addressSql(String attr, SqlBuilderHandler sqlBuilder, String province, String city, String district, String logicalSymbol, String connector, boolean containsAddress) {
        //兼容穿透查询
        if (containsAddress) {
            //值放在了第一个位置
            if (province != null) {
                String[] attrKey = attr.split("\\.");
                sqlBuilder.append(connector).append(XbbConstant.SPACE)
                        .append(JSON_EXTRACT_STRING).append("(").append(JSON_EXTRACT_RAW).append("(data," + "'").append(attrKey[0]).append("'").append(")").append(",").append("'").append(attrKey[1]).append("'").append(")").append(logicalSymbol)
                        .append("'").append(province).append("'");
            }
        } else {
            if (province != null) {
                sqlBuilder.append(connector).append(XbbConstant.SPACE)
                        .append(JSON_EXTRACT_STRING).append("(").append(JSON_EXTRACT_RAW).append("(data," + "'").append(attr).append("'").append(")").append(",").append("'").append(BusinessConstant.PROVINCE).append("'").append(")").append(logicalSymbol)
                        .append("'").append(province).append("'");
                if (city != null) {
                    sqlBuilder.append(connector).append(XbbConstant.SPACE)
                            .append(JSON_EXTRACT_STRING).append("(").append(JSON_EXTRACT_RAW).append("(data," + "'").append(attr).append("'").append(")").append(",").append("'").append(BusinessConstant.CITY).append("'").append(")").append(logicalSymbol)
                            .append("'").append(city).append("'");
                    if (district != null) {
                        sqlBuilder.append(connector).append(XbbConstant.SPACE)
                                .append(JSON_EXTRACT_STRING).append("(").append(JSON_EXTRACT_RAW).append("(data," + "'").append(attr).append("'").append(")").append(",").append("'").append(BusinessConstant.DISTRICT).append("'").append(")").append(logicalSymbol)
                                .append("'").append(district).append("'");
                    }
                }
            }
        }
    }


    /**
     * 公用解析（特殊的请单独写）
     *
     * @param conditionEnum
     * @param condition
     */
    public void commonConditionSymbolChoose(String corpid, SqlBuilderHandler sqlBuilder, ConditionEnum conditionEnum,
                                            ConditionsEntityExt condition, boolean isSingle, Integer businessType) {
        String attr = condition.getAttr();
        Integer fieldType = condition.getFieldType();
        FieldTypeEnum fieldTypeEnum = FieldTypeEnum.getFieldTypeEnum(fieldType);
        if (!isSingle) {
            attr = Objects.isNull(condition.getOriginalAttr()) ? attr : condition.getOriginalAttr();
        }
        if (!attr.contains(StringConstant.POINT)) {
            attr = StringUtil.getDataBaseString(attr);
        }
        Long formId = condition.getFormId();
        // text特殊处理
        Object value = null;
        Object secondVal = null;
        List<Object> valList = condition.getValue();
        if (CollectionsUtil.isNotEmpty(valList)) {
            // 文本转换
            for (int i = 0, valListSize = valList.size(); i < valListSize; i++) {
                Object val = CommonHelper.handleText(fieldType, valList.get(i));
                valList.set(i, val);
            }
            // 第一个值
            value = valList.get(0);
            // 第二个值
            if (valList.size() > 1) {
                secondVal = valList.get(1);
            }
        }

        //生成初始的sql拼装语句
        sqlBuilder = initSqlString(corpid, formId, condition.getBusinessType(), sqlBuilder, attr);
        switch (conditionEnum) {
            case EQUAL:
                if (valList.size() == 1) {
                    if (Objects.equals(FieldTypeEnum.ADDRESS.getType(), fieldType)
                            || Objects.equals(FieldTypeEnum.LOCATION.getType(), fieldType)) {
                        // and  JSONExtractString(JSONExtractRaw(data,'address_1'),'city') ='杭州市'
                        Map<String, Objects> addressJson = (Map) value;
                        //stringBuffer.append(XbbConstant.AND).append(XbbConstant.SPACE).append("(").append(JSON_EXTRACT_STRING).append("(").append(JSON_EXTRACT_RAW).append("(data," + "'").append(attr).append("'").append(")").append("'").append(BusinessConstant.CITY    ).append("'").append(" = ").append("'").append(addressJson.get(BusinessConstant.CITY)).append("'");
                        sqlBuilder.and(XbbConstant.L_P,JSON_EXTRACT_STRING,XbbConstant.L_P,JSON_EXTRACT_RAW,
                                "(data," + "'",attr,XbbConstant.SINGLE_QUOTES,XbbConstant.R_P,XbbConstant.SINGLE_QUOTES,
                                BusinessConstant.CITY,XbbConstant.SINGLE_QUOTES,XbbConstant.EQUAL,XbbConstant.SINGLE_QUOTES,
                                StringHelper.getString(addressJson.get(BusinessConstant.CITY)),XbbConstant.SINGLE_QUOTES);
                        //stringBuffer.append(XbbConstant.AND).append(XbbConstant.SPACE).append("(").append(JSON_EXTRACT_STRING).append("(").append(JSON_EXTRACT_RAW).append("(data," + "'").append(attr).append("'").append(")").append("'").append(BusinessConstant.DISTRICT).append("'").append(" = ").append("'").append(addressJson.get(BusinessConstant.DISTRICT)).append("'");
                        sqlBuilder.and(XbbConstant.L_P,JSON_EXTRACT_STRING,XbbConstant.L_P,JSON_EXTRACT_RAW,
                                "(data," + "'",attr,XbbConstant.SINGLE_QUOTES,XbbConstant.R_P,XbbConstant.SINGLE_QUOTES,
                                BusinessConstant.DISTRICT,XbbConstant.SINGLE_QUOTES,XbbConstant.EQUAL,XbbConstant.SINGLE_QUOTES,
                                StringHelper.getString(addressJson.get(BusinessConstant.DISTRICT)),XbbConstant.SINGLE_QUOTES);
                        sqlBuilder.and(XbbConstant.L_P,JSON_EXTRACT_STRING,XbbConstant.L_P,JSON_EXTRACT_RAW,
                                "(data," + "'",attr,XbbConstant.SINGLE_QUOTES,XbbConstant.R_P,XbbConstant.SINGLE_QUOTES,
                                BusinessConstant.ADDRESS,XbbConstant.SINGLE_QUOTES,XbbConstant.EQUAL,XbbConstant.SINGLE_QUOTES,
                                StringHelper.getString(addressJson.get(BusinessConstant.ADDRESS)),XbbConstant.SINGLE_QUOTES);


                        //stringBuffer.append(XbbConstant.AND).append(XbbConstant.SPACE).append("(").append(JSON_EXTRACT_STRING).append("(").append(JSON_EXTRACT_RAW).append("(data," + "'").append(attr).append("'").append(")").append("'").append(BusinessConstant.ADDRESS).append("'").append(" = ").append("'").append(addressJson.get(BusinessConstant.ADDRESS)).append("'");
                    } else if (Objects.equals(FieldTypeEnum.COMBO_CHECK.getType(), fieldType) || Objects.equals(FieldTypeEnum.CHECKBOX_GROUP.getType(), fieldType)
                            || Objects.equals(FieldTypeEnum.USER_GROUP.getType(), fieldType) || Objects.equals(FieldTypeEnum.DEPT_GROUP.getType(), fieldType) || Objects.equals(FieldTypeEnum.LABEL.getType(), fieldType)) {
                        // 多选框只有一个值时,外层限制了等于
//                        select id,JSONExtractArrayRaw(`data`,'text_20') FROM  tb_saas_customer prewhere  corpid ='1'
//                        AND  has(JSONExtractArrayRaw(`data`,'text_20'),'"国庆"')
//                         AND  hasAll(JSONExtractArrayRaw(`data`,'text_20'),['"中秋"','"国庆"'])
                        if (BIHelp.setInData(fieldType)) {
                            String leftMarks = "['\"";
                            String rightMarks = "\"'])";
                            if (Objects.equals(FieldTypeEnum.DEPT_GROUP.getType(), fieldType)) {
                                leftMarks = "['";
                                rightMarks = "'])";
                            }
                            if (attr.contains(StringConstant.POINT)) {
                                String[] attrArray = attr.split("\\.");
                                String subForm = attrArray[0];
                                if (StringUtils.isNotEmpty(subForm) && subForm.contains("_")) {
                                    if (isNeedRelationSubFormTable(businessType, subForm)) {
                                        //stringBuffer.append(XbbConstant.AND).append("hasAll(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attrArray[1] + "'), " + leftMarks + value + rightMarks).append(XbbConstant.SPACE);
                                        sqlBuilder.and("hasAll(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attrArray[1] + "'), " + leftMarks + value + rightMarks);
                                        //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "(" + leftMarks + value + rightMarks).append(" = ").append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attrArray[1] + "')" + ")");
                                        //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "(" + leftMarks + value + rightMarks).append(" = ").append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attrArray[1] + "')" + ")");
                                        sqlBuilder.and(XbbConstant.LENGTH + "(" + leftMarks + value + rightMarks,XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attrArray[1] + "')" + ")");
                                    } else {
                                        //stringBuffer.append(XbbConstant.AND).append("hasAll(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "'), " + leftMarks + value + rightMarks).append(XbbConstant.SPACE);
                                        sqlBuilder.and("hasAll(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "'), " + leftMarks + value + rightMarks);
                                        //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "(" + leftMarks + value + rightMarks).append(" = ").append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "')" + ")");
                                        sqlBuilder.and(XbbConstant.LENGTH + "(" + leftMarks + value + rightMarks,XbbConstant.EQUAL,XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "')" + ")");
                                    }
                                }
                            } else {
                                //stringBuffer.append(XbbConstant.AND).append("hasAll(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "'), " + leftMarks + value + rightMarks).append(XbbConstant.SPACE);
                                sqlBuilder.and("hasAll(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "'), " + leftMarks + value + rightMarks);
                                //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "(" + leftMarks + value + rightMarks).append(" = ").append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "')" + ")");
                                sqlBuilder.and(XbbConstant.LENGTH + "(" + leftMarks + value + rightMarks,XbbConstant.EQUAL,XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "')" + ")");

                            }
                        }
                    } else if (value instanceof List) {
                        //TODO
                        /*select id,JSONExtractArrayRaw(`data`,'text_20') FROM  tb_saas_customer prewhere  corpid ='1'
                        AND  hasAll(JSONExtractArrayRaw(`data`,'text_20'),['"新建一条客户吧"','"国庆"','"中秋"'])
                        AND LENGTH (['"新建一条客户吧"','"国庆"','"中秋"']) = LENGTH (JSONExtractArrayRaw(`data`,'text_20'))*/
                        if (BIHelp.setInData(fieldType)) {
                            //TODO hasAll
                            //stringBuffer.append(XbbConstant.AND).append("has(+" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "'), " + "'" + value + "')").append(XbbConstant.SPACE);
                            sqlBuilder.and("has(+" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "'), " + "'" + value + "')");
                            //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "(" + value + ")").append(" = ").append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,' " + attr + "')'" + ")");
                            sqlBuilder.and(XbbConstant.LENGTH + "(" + value + ")",XbbConstant.EQUAL,XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,' " + attr + "')'" + ")");
                        }
                        //TODO 当前if选项内应该都在data内

                    } else if (Objects.equals(FieldTypeEnum.SWITCH.getType(), fieldType)) {
                        if (BIHelp.setInData(fieldType)) {
                            attr = XbbConstant.JSON_EXTRACT_INT + "(`data`,'" + attr + "')";
                        }
                        //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" = ").append(value);
                        sqlBuilder.and(attr,XbbConstant.EQUAL,value);
                    } else if (Objects.equals(FieldTypeEnum.LINK_BUSINESS_SINGLE.getType(), fieldType)) {
                        // 关联单选存的是只有long类型和String
                        if (BIHelp.setInData(fieldType)) {
                            String s = JSON_EXTRACT;
                            if (!(value instanceof String)) {
                                s = XbbConstant.JSON_EXTRACT_INT;
                            }
                            attr = getStringWithFunN(s, attr);
                        }
                        //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" = ").append(value);
                        sqlBuilder.and(attr,XbbConstant.EQUAL,value);
                    } else if (Objects.equals(FieldTypeEnum.COMBO_NUM.getType(), fieldType)) {
                        if (Objects.equals(businessType, XbbRefTypeEnum.CONTRACT_PERFORMANCE.getCode())) {
                            attr = StringUtil.getDataBaseString(ContractPerformanceEnum.getRealAttr(attr));
                        } else {
                            attr = XbbConstant.JSON_EXTRACT_INT + "(`data`,'" + attr + "') ";
                        }
                        //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" = ").append(value);
                        sqlBuilder.and(attr,XbbConstant.EQUAL,value);
                    } else {
                        // 简单的相等
                        if (BIHelp.setInData(fieldType)) {
                            attr = getFunctionAttr(attr, fieldType, value, null, businessType, sqlBuilder);
                        } else {
                            if (value instanceof String) {
                                attr = StringUtil.getDataBaseString(attr);
                            }
                        }
                        if (attr != null && StringUtils.contains(attr, XbbConstant.ARRAY_ELEMENT)) {
                            handlerMainSubForm(sqlBuilder, attr, XbbConstant.EQUAL, value);
                            break;
                        }
                        //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" = ").append(value);
                        sqlBuilder.and(attr,XbbConstant.EQUAL,value);
                    }

                } else {
                    if (BIHelp.setInData(fieldType)) {
                        String s = JSON_EXTRACT_RAW;
                        attr = getStringWithFunN(s, attr);
                    }
                    if (CollectionsUtil.isNotEmpty(valList)) {
                        //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" IN ").append(valList);
                        sqlBuilder.and(attr,XbbConstant.IN,valList);
                    }
                }
                break;
            case NOEQUAL:
                //SELECT JSONExtractRaw('{"a": 0, "b": [-100, 200.0, 300]}', 'a') = '[-100, 200.0, 300]';
                //TODO 也要根据类型区分使用的函数
                if (BIHelp.setInData(fieldType)) {
                    /*String s = XbbConstant.JSON_EXTRACT_RAW;
                    FieldTypeEnum fieldTypeE = FieldTypeEnum.getFieldTypeEnum(fieldType);
                    if (!StringUtils.equals(fieldTypeE.getEsalias(), EsAliasEnum.TEXT.getEsAlias())) {
                        attr = s + "(`data`,'" + attr + "') ";
                    }else {
                        attr = s + "(`data`,'" + attr + "') ";
                    }*/
                    attr = getFunctionAttr(attr, fieldType, value, null, businessType, sqlBuilder);
                    //String extractFuntion = getExtractFuntion(fieldType, value);

                }
                if (attr != null && StringUtils.contains(attr, XbbConstant.ARRAY_ELEMENT)) {
                    handlerMainSubForm(sqlBuilder, attr, XbbConstant.NOT_EQUAL, value);
                    break;
                }
                //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" != ").append(value);
                sqlBuilder.and(attr,XbbConstant.NOT_EQUAL,value);
                break;
            case EMPTY:
                String str = attr;
                FieldTypeEnum fieldTypeE = FieldTypeEnum.getFieldTypeEnum(fieldType);
                if (fieldTypeE != null) {
                    String esalias = fieldTypeE.getEsalias();
                    if (Objects.equals(FieldTypeEnum.NUM, fieldTypeE) || (StringUtils.equals(esalias, EsAliasEnum.LONG.getEsAlias()) ||
                            StringUtils.equals(esalias, EsAliasEnum.DATE.getEsAlias()))) {
                        //stringBuffer.append(XbbConstant.AND).append(" JSONHas(data, '" + attr + "') = 0");
                        sqlBuilder.and(" JSONHas(data, '" + attr + "') = 0");
                        break;
                    }
                }

                if (BIHelp.setInData(fieldType)) {
                    //str = XbbConstant.JSON_EXTRACT_STRING + "(`data`,'" + attr + "')";
                    str = getFunctionAttr(attr, fieldType, value, JSON_EXTRACT, businessType, sqlBuilder);
                }
                if (FieldTypeEnum.DATETIME.getType().equals(fieldType)
                        || FieldTypeEnum.STAR.getType().equals(fieldType)
                        || FieldTypeEnum.DEPARTMENTID.getType().equals(fieldType)
                        || FieldTypeEnum.ADDTIME.getType().equals(fieldType)
                        || FieldTypeEnum.LINK_DATA.getType().equals(fieldType)
                        || FieldTypeEnum.UPDATETIME.getType().equals(fieldType)) {
                    //stringBuffer.append(XbbConstant.AND).append(str).append(XbbConstant.SPACE).append(" = ").append(0);
                    sqlBuilder.and(str,XbbConstant.EQUAL,0);
                } else if (fieldTypeEnum != null
                        && (FieldTypeEnum.TEXT.getEsalias().equals(fieldTypeEnum.getEsalias())
                        || FieldTypeEnum.ADDRESS.getEsalias().equals(fieldTypeEnum.getEsalias())
                        || FieldTypeEnum.UPLOAD.getEsalias().equals(fieldTypeEnum.getEsalias())
                        || FieldTypeEnum.NUM.getEsalias().equals(fieldTypeEnum.getEsalias()))) {
                    if (str != null && StringUtils.contains(str, XbbConstant.ARRAY_ELEMENT)) {
                        handlerMainSubForm(sqlBuilder, str, XbbConstant.EQUAL, "''");
                        break;
                    }

                    //stringBuffer.append(XbbConstant.AND).append(str).append(XbbConstant.SPACE).append(" = ").append("''");
                    sqlBuilder.and(str,XbbConstant.EQUAL,XbbConstant.SINGLE_QUOTES,XbbConstant.SINGLE_QUOTES);
                } else if (Objects.equals(FieldTypeEnum.COMBO_CHECK.getType(), fieldType) || Objects.equals(FieldTypeEnum.CHECKBOX_GROUP.getType(), fieldType)
                        || Objects.equals(FieldTypeEnum.USER_GROUP.getType(), fieldType) || Objects.equals(FieldTypeEnum.DEPT_GROUP.getType(), fieldType) || Objects.equals(FieldTypeEnum.LABEL.getType(), fieldType)) {
                    //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "')" + ") = 0 ");
                    handlerSubformComboEmpty(attr, sqlBuilder, "=", businessType);
                    //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + str + "')" + ") = 0 ");
                } else {
                    // 空数组也只需这个
                    //stringBuffer.append(XbbConstant.AND).append(str).append(XbbConstant.SPACE).append("IS NULL");
                    sqlBuilder.and(str,"IS NULL");
                }
                break;
            case NOEMPTY:
                String noEmptyStr = attr;
                FieldTypeEnum fieldTypeN = FieldTypeEnum.getFieldTypeEnum(fieldType);
                if (fieldTypeN != null) {
                    String esalias = fieldTypeN.getEsalias();
                    if (Objects.equals(FieldTypeEnum.NUM, fieldTypeN) || (StringUtils.equals(esalias, EsAliasEnum.LONG.getEsAlias()) ||
                            StringUtils.equals(esalias, EsAliasEnum.DATE.getEsAlias()))) {
                        //stringBuffer.append(XbbConstant.AND).append(" JSONHas(data, '" + attr + "') = 1");
                        sqlBuilder.and(" JSONHas(data, '" + attr + "') = 1");
                        break;
                    }
                }
                if (BIHelp.setInData(fieldType)) {
                    //noEmptyStr = XbbConstant.JSON_EXTRACT_STRING + "(`data`,'" + attr + "')";
                    noEmptyStr = getFunctionAttr(attr, fieldType, value, JSON_EXTRACT, businessType, sqlBuilder);
                }
                if (FieldTypeEnum.DATETIME.getType().equals(fieldType)
                        || FieldTypeEnum.STAR.getType().equals(fieldType)
                        || FieldTypeEnum.DEPARTMENTID.getType().equals(fieldType)
                        || FieldTypeEnum.ADDTIME.getType().equals(fieldType)
                        || FieldTypeEnum.LINK_DATA.getType().equals(fieldType)
                        || FieldTypeEnum.UPDATETIME.getType().equals(fieldType)) {
                    //stringBuffer.append(XbbConstant.AND).append(noEmptyStr).append(XbbConstant.SPACE).append(" != ").append(0);
                    sqlBuilder.and(noEmptyStr,XbbConstant.NOT_EQUAL,0);
                } else if (fieldTypeEnum != null
                        && (FieldTypeEnum.TEXT.getEsalias().equals(fieldTypeEnum.getEsalias())
                        || FieldTypeEnum.ADDRESS.getEsalias().equals(fieldTypeEnum.getEsalias())
                        || FieldTypeEnum.UPLOAD.getEsalias().equals(fieldTypeEnum.getEsalias())
                        || FieldTypeEnum.NUM.getEsalias().equals(fieldTypeEnum.getEsalias()))) {
                    if (noEmptyStr != null && StringUtils.contains(noEmptyStr, XbbConstant.ARRAY_ELEMENT)) {
                        handlerMainSubForm(sqlBuilder, noEmptyStr, XbbConstant.NOT_EQUAL, "''");
                        break;
                    }
                    //stringBuffer.append(XbbConstant.AND).append(noEmptyStr).append(XbbConstant.SPACE).append(" != ").append("''");
                    sqlBuilder.and(noEmptyStr,XbbConstant.NOT_EQUAL,XbbConstant.SINGLE_QUOTES,XbbConstant.SINGLE_QUOTES);
                } else if (Objects.equals(FieldTypeEnum.COMBO_CHECK.getType(), fieldType) || Objects.equals(FieldTypeEnum.CHECKBOX_GROUP.getType(), fieldType)
                        || Objects.equals(FieldTypeEnum.USER_GROUP.getType(), fieldType) || Objects.equals(FieldTypeEnum.DEPT_GROUP.getType(), fieldType) || Objects.equals(FieldTypeEnum.LABEL.getType(), fieldType)) {
                    //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "')" + ") > 0 ");
                    handlerSubformComboEmpty(attr, sqlBuilder, ">", businessType);
                } else {
                    //stringBuffer.append(XbbConstant.AND).append(noEmptyStr).append(XbbConstant.SPACE).append("IS NOT NULL");
                    sqlBuilder.and(noEmptyStr,"IS NOT NULL");
                }
                break;
            case INCLUDE:
                if (Objects.equals(FieldTypeEnum.COMBO_CHECK.getType(), fieldType) || Objects.equals(FieldTypeEnum.CHECKBOX_GROUP.getType(), fieldType)
                        || Objects.equals(FieldTypeEnum.USER_GROUP.getType(), fieldType) || Objects.equals(FieldTypeEnum.DEPT_GROUP.getType(), fieldType) || Objects.equals(FieldTypeEnum.LABEL.getType(), fieldType)) {
                    // 多选框只有一个值时,外层限制了等于
//                        select id,JSONExtractArrayRaw(`data`,'text_20') FROM  tb_saas_customer prewhere  corpid ='1'
//                        AND  has(JSONExtractArrayRaw(`data`,'text_20'),'"国庆"')
//                         AND  hasAll(JSONExtractArrayRaw(`data`,'text_20'),['"中秋"','"国庆"'])
                    if (BIHelp.setInData(fieldType)) {
                        StringBuilder s = new StringBuilder();
                        if (valList.size() > 0) {
                            s.append("( ");
                            String marks = "['\"";
                            String marksSuff = "\"'])";
                            if (Objects.equals(FieldTypeEnum.DEPT_GROUP.getType(), fieldType)) {
                                marks = "['";
                                marksSuff = "'])";
                            }
                            for (int i = 0; i < valList.size(); i++) {
                                if (i > 0) {
                                    s.append(" or ");
                                }
                                if (attr.contains(StringConstant.POINT)) {
                                    String[] attrArray = attr.split("\\.");
                                    String attr1;
                                    String subForm = attrArray[0];
                                    if (StringUtils.isNotEmpty(subForm) && subForm.indexOf("_") != -1) {
                                        if (isNeedRelationSubFormTable(businessType, subForm)) {
                                            attr1 = JSON_EXTRACT_ARRAY_RAW + "(data,'" + attrArray[1] + "')";
                                            s.append("hasAll(").append(attr1)
                                                    .append(", ").append(marks).append(valList.get(i)).append(marksSuff);
                                        } else {
                                            attr1 = attrArray[1];
                                            String arrayRaw = "arrayJoin(" + JSON_EXTRACT_ARRAY_RAW + "(data,'" + attrArray[0] + "'))";
                                            s.append("hasAll(").append(JSON_EXTRACT_ARRAY_RAW).append("(" + arrayRaw + ",'").append(attr1)
                                                    .append("'), ").append(marks).append(valList.get(i)).append(marksSuff);
                                        }
                                    }

                                } else {
                                    s.append("hasAll(").append(JSON_EXTRACT_ARRAY_RAW).append("(`data`,'").append(attr)
                                            .append("'), ").append(marks).append(valList.get(i)).append(marksSuff);
                                }
                            }
                            s.append(" )");
                        }
                        //stringBuffer.append(XbbConstant.AND).append(s).append(XbbConstant.SPACE);
                        sqlBuilder.and(s);
                    }
                } else {
                    Set<Object> valueSet = new HashSet<>();
                    for (Object obj : valList) {
                        if (Objects.nonNull(obj)) {
                            valueSet.add(obj);
                        }
                    }
                    if (!valueSet.isEmpty()) {
                        if (BIHelp.setInData(fieldType)) {
                            attr = getFunctionAttr(attr, fieldType, value, JSON_EXTRACT, businessType, sqlBuilder);
                        }
                        //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" IN ").append(valueSet);
                        sqlBuilder.and(attr,XbbConstant.IN,valList);
                    } else {
                        //stringBuffer.append(XbbConstant.AND).append(" 1=0 ");
                        sqlBuilder.and(" 1=0 ");
                    }
                }
                break;
            case LIKE:
                if (BIHelp.setInData(fieldType)) {
                    //attr = XbbConstant.JSON_EXTRACT_STRING + "(`data`,'" + attr + "')";
                    attr = getFunctionAttr(attr, fieldType, value, JSON_EXTRACT, businessType, sqlBuilder);
                }
                if (value instanceof String) {
                    String substring = value.toString().substring(1, value.toString().length() - 1);
                    value = "'%" + substring + "%'";
                }
                if (attr != null && StringUtils.contains(attr, "arrayElement")) {
                    handlerMainSubForm(sqlBuilder, attr, XbbConstant.LIKE, value);
                    break;
                }
                //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" LIKE ").append(value);
                sqlBuilder.and(attr,XbbConstant.LIKE,value);
                break;
            case NOLIKE:
                if (BIHelp.setInData(fieldType)) {
                    //attr = XbbConstant.JSON_EXTRACT_STRING + "(`data`,'" + attr + "')";
                    attr = getFunctionAttr(attr, fieldType, value, JSON_EXTRACT, businessType, sqlBuilder);
                }
                if (value instanceof String) {
                    String substring = value.toString().substring(1, value.toString().length() - 1);
                    value = "'%" + substring + "%'";
                }
                if (attr != null && StringUtils.contains(attr, XbbConstant.ARRAY_ELEMENT)) {
                    handlerMainSubForm(sqlBuilder, attr, XbbConstant.NOT_LIKE, value);
                    break;
                }
                //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" NOT LIKE ").append(value);
                sqlBuilder.and(attr,XbbConstant.NOT_LIKE,value);
                break;
            case ALLINCLUDE:
                if (BIHelp.setInData(fieldType)) {
                    if (Objects.equals(FieldTypeEnum.COMBO_CHECK.getType(), fieldType) || Objects.equals(FieldTypeEnum.CHECKBOX_GROUP.getType(), fieldType)
                            || Objects.equals(FieldTypeEnum.USER_GROUP.getType(), fieldType) || Objects.equals(FieldTypeEnum.DEPT_GROUP.getType(), fieldType) || Objects.equals(FieldTypeEnum.LABEL.getType(), fieldType)) {
                        if (BIHelp.setInData(fieldType)) {
                            String vals = "";
                            if (valList != null) {
                                vals = valList.stream().map(e -> "'\"" + e + "\"'").collect(Collectors.joining(","));
                                if (Objects.equals(FieldTypeEnum.DEPT_GROUP.getType(), fieldType)) {
                                    vals = valList.stream().map(e -> "'" + e + "'").collect(Collectors.joining(","));
                                }
                            }
                            /*if (attr.contains(StringConstant.POINT)) {
                                String[] attrArray = attr.split("\\.");
                                attr = attrArray[1];
                                String array_raw = "arrayJoin("+JSON_EXTRACT_ARRAY_RAW+ "(data,'" + attrArray[0] + "'))";
                                stringBuffer.append(XbbConstant.AND).append("hasAll(" + JSON_EXTRACT_ARRAY_RAW + "("+array_raw+",'" + attr + "'), " + "[" + vals + "])").append(XbbConstant.SPACE);
                                stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "([" + vals + "])")       .append(" = ").append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "("+array_raw+",'" + attr + "')" + ")");
                            }*/
                            if (attr.contains(StringConstant.POINT)) {
                                String[] attrArray = attr.split("\\.");
                                String attr1;
                                String subForm = attrArray[0];
                                if (StringUtils.isNotEmpty(subForm) && subForm.indexOf("_") != -1) {
                                    if (isNeedRelationSubFormTable(businessType, subForm)) {
                                        attr1 = JSON_EXTRACT_ARRAY_RAW + "(data,'" + attrArray[1] + "')";
                                        //stringBuffer.append(XbbConstant.AND).append("hasAll(" + attr1 + ", " + "[" + vals + "])").append(XbbConstant.SPACE);
                                        sqlBuilder.and("hasAll(" + attr1 + ", " + "[" + vals + "])");
                                        //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "([" + vals + "])").append(" = ").append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(data,'" + attrArray[1] + "')" + ")");
                                        sqlBuilder.and(XbbConstant.LENGTH + "([" + vals + "])",XbbConstant.EQUAL,XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(data,'" + attrArray[1] + "')" + ")");
                                    } else {
                                        attr = attrArray[1];
                                        String arrayRaw = "arrayJoin(" + JSON_EXTRACT_ARRAY_RAW + "(data,'" + attrArray[0] + "'))";
                                        //stringBuffer.append(XbbConstant.AND).append("hasAll(" + JSON_EXTRACT_ARRAY_RAW + "(" + arrayRaw + ",'" + attr + "'), " + "[" + vals + "])").append(XbbConstant.SPACE);
                                        sqlBuilder.and("hasAll(" + JSON_EXTRACT_ARRAY_RAW + "(" + arrayRaw + ",'" + attr + "'), " + "[" + vals + "])");
                                        //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "([" + vals + "])").append(" = ").append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(" + arrayRaw + ",'" + attr + "')" + ")");
                                        sqlBuilder.and(XbbConstant.LENGTH + "([" + vals + "])",XbbConstant.EQUAL,XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(" + arrayRaw + ",'" + attr + "')" + ")");
                                    }
                                }
                            } else {
                                //StringJoiner stringJoiner = new StringJoiner(",","[","]");
                                //stringBuffer.append(XbbConstant.AND).append("hasAll(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "'), " + "[" + vals + "])").append(XbbConstant.SPACE);
                                sqlBuilder.and("hasAll(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "'), " + "[" + vals + "])");
                                //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "([" + vals + "])").append(" = ").append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "')" + ")");
                                sqlBuilder.and(XbbConstant.LENGTH + "([" + vals + "])",XbbConstant.EQUAL,XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "')" + ")");
                            }
                            /*} else {
                                stringBuffer.append(XbbConstant.AND).append("hasAll(" + XbbConstant.JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "'), " + "['\"" + value + "\"'])").append(XbbConstant.SPACE);
                                stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "(['\"" + value + "\"'])").append(" = ").append(XbbConstant.LENGTH + "(" + XbbConstant.JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "')" + ")");
                            }*/
                        }
                    } else {
                        //stringBuffer.append(XbbConstant.AND).append("has(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "'), " + "'" + valList + "')").append(XbbConstant.SPACE);
                        sqlBuilder.and("has(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "'), " + "'" + valList + "')");
                        //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "(" + valList + ")").append(" = ").append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,' " + attr + "')'" + ")");
                        sqlBuilder.and(XbbConstant.LENGTH + "(" + valList + ")",XbbConstant.EQUAL,XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,' " + attr + "')'" + ")");

                    }
                }
                break;
            case IN:
                if (BIHelp.setInData(fieldType)) {
                    /*String s = getExtractFuntion(fieldType, value);
                    attr = getStringWithFunN(s,attr);*/
                    attr = getFunctionAttr(attr, fieldType, value, null, businessType, sqlBuilder);
                }
                //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" IN ").append(valList);
                sqlBuilder.and(attr,XbbConstant.IN,valList);
                break;
            case NOIN:
                if (BIHelp.setInData(fieldType)) {
                    /*String s = getExtractFuntion(fieldType, value);
                    attr = getStringWithFunN(s,attr);*/
                    attr = getFunctionAttr(attr, fieldType, value, null, businessType, sqlBuilder);
                }
                //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" NOT IN ").append(valList);
                sqlBuilder.and(attr,XbbConstant.NOT_IN,valList);
                break;
            case GREATEREQUAL:
                //TODO GREATEREQUAL和GREATERTHAN为什么不一样
                if (Objects.isNull(value) || value.toString().isEmpty()) {
                    return;
                }
                if (BIHelp.setInData(fieldType)) {
                        /*if (attr.contains(StringConstant.POINT)) {
                            String[] attrArray = attr.split("\\.");
                            //attr = attrArray[1];
                            String array_raw = "arrayJoin("+JSON_EXTRACT_ARRAY_RAW+ "(data,'" + attrArray[0] + "'))";
                            for (int i = 1; i < attrArray.length; i++) {
                                //attr  = getExtractFuntion(fieldType,attr) + "("+array_raw+",'" + attr + "')";
                                attr = attrArray[i];
                                attr  = getExtractFuntion(fieldType,attr) + "("+array_raw+",'" + attr + "')";

                            }

                            //JSONExtractInt(arrayJoin(JSONExtractArrayRaw(data,'subForm_2')),'text_1')
                        }else {
                            attr = XbbConstant.JSON_EXTRACT_INT + "(`data`,'" + attr + "')";
                        }*/
                    attr = getFunctionAttr(attr, fieldType, value, XbbConstant.JSON_EXTRACT_DOUBLE, businessType, sqlBuilder);
                }
                //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" >= ").append(value);
                sqlBuilder.and(attr,XbbConstant.GREATER_EQUAL,value);
                break;
            case GREATERTHAN:
                if (Objects.isNull(value) || value.toString().isEmpty()) {
                    return;
                }
                greaterSql(condition, attr, fieldType, sqlBuilder, " > ", corpid, businessType);
                break;
            case LESSEQUAL:
                if (Objects.isNull(value) || value.toString().isEmpty()) {
                    return;
                }
                greaterSql(condition, attr, fieldType, sqlBuilder, " <= ", corpid, businessType);
                break;
            case LESSTHAN:
                if (Objects.isNull(value) || value.toString().isEmpty()) {
                    return;
                }
                if (BIHelp.setInData(fieldType)) {
                    //attr = XbbConstant.JSON_EXTRACT_DOUBLE + "(`data`,'" + attr + "')";
                    attr = getFunctionAttr(attr, fieldType, value, XbbConstant.JSON_EXTRACT_DOUBLE, businessType, sqlBuilder);
                }
                //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" < ").append(value);
                sqlBuilder.and(attr,XbbConstant.LESS_THAN,value);
                break;
            case RANGE:
                if ((Objects.isNull(value) || value.toString().isEmpty())
                        || (Objects.isNull(secondVal) || secondVal.toString().isEmpty())) {
                    return;
                }
                if (BIHelp.setInData(fieldType)) {
                    //attr = XbbConstant.JSON_EXTRACT_INT + "(`data`,'" + attr + "')";
                    attr = getFunctionAttr(attr, fieldType, value, XbbConstant.JSON_EXTRACT_DOUBLE, businessType, sqlBuilder);
                }
                //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" >= ").append(value);
                sqlBuilder.and(attr,XbbConstant.GREATER_EQUAL,value);
                //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" < ").append(secondVal);
                sqlBuilder.and(attr,XbbConstant.LESS_THAN,secondVal);
                break;
            case NORANGE:
                if ((Objects.isNull(value) || value.toString().isEmpty())
                        || (Objects.isNull(secondVal) || secondVal.toString().isEmpty())) {
                    return;
                }
                //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" < ").append(value);
                sqlBuilder.and(attr,XbbConstant.LESS_THAN,value);
                //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(" >= ").append(secondVal);
                sqlBuilder.and(attr,XbbConstant.GREATER_EQUAL,secondVal);
                break;
            default:
                break;
        }
    }


    /**
     * @param attr
     * @param fieldType
     * @param value
     * @param functionName
     * @param businessType
     * @param sqlBuilder
     * @return java.lang.String
     * @throws
     * @Description 添加子表单处理逻辑
     * @Date 16:56 2022/1/11
     * @Param
     **/

    private static String getFunctionAttr(String attr, Integer fieldType, Object value, String functionName, Integer businessType, SqlBuilderHandler sqlBuilder) {
        if (StringUtils.isNotEmpty(attr) && attr.contains(StringConstant.POINT)) {
            String[] attrArray = attr.split("\\.");
            String subForm = attrArray[0];
            if (StringUtils.isNotEmpty(subForm) && subForm.indexOf("_") != -1) {
                if (isNeedRelationSubFormTable(businessType, subForm)) {
                    String attrStr = " attr = '" + subForm + "' AND ";
                    String sql = sqlBuilder.end().getSql();
                    if (null != sql && sql.indexOf(attrStr) != -1) {
                        attrStr = "";
                    }
                    String extractFuntion = getExtractFuntion(fieldType, value);
                    //attr = attrStr + extractFuntion + "(data,'" + attrArray[1] + "')";
                    attr = String.format("%s%s", attrStr, getExtractString(extractFuntion, null, attrArray[1]));

                } else {
                    attr = "empty(arrayElement(arrayFilter((x -> " + JSON_EXTRACT + "(x,'" + attrArray[1] + "','"+XbbConstant.STRING_CONSTANT+"')  %s %s), " + JSON_EXTRACT_ARRAY_RAW + "(data,'" + attrArray[0] + "')),1)) = 0";
                    /*String array_raw = "arrayJoin("+JSON_EXTRACT_ARRAY_RAW+ "(data,'" + attrArray[0] + "'))";
                    for (int i = 1; i < attrArray.length; i++) {
                        //attr  = getExtractFuntion(fieldType,attr) + "("+array_raw+",'" + attr + "')";
                        attr = attrArray[i];
                        attr = getExtractFuntion(fieldType,value) + "("+array_raw+",'" + attr + "')";
                    }*/
                }
            }
            //JSONExtractInt(arrayJoin(JSONExtractArrayRaw(data,'subForm_2')),'text_1')
        } else {
            if (StringUtils.isEmpty(functionName)) {
                functionName = getExtractFuntion(fieldType, value);
            }
            if (Objects.equals(XbbRefTypeEnum.CONTRACT_PERFORMANCE.getCode(), businessType)) {
                attr = StringUtil.getDataBaseString(ContractPerformanceEnum.getRealAttr(attr));
            } else {
                attr = getStringWithFunN(functionName, attr);
            }
        }
        return attr;
    }

    /**
     * @return java.lang.String
     * @throws
     * @Description 根据filedType。value值确定抽取函数名
     * @Date 19:34 2021/12/13
     * @Param [fieldType, value]
     **/

    public static String getExtractFuntion(Integer fieldType, Object value) {
        String funName = JSON_EXTRACT;
        FieldTypeEnum fieldTypeE = FieldTypeEnum.getFieldTypeEnum(fieldType);
        if (fieldTypeE != null) {
            String esalias = fieldTypeE.getEsalias();
            if (StringUtils.equals(esalias, EsAliasEnum.NUM.getEsAlias())) {
                funName = getNumExtractFuntion(value);
            } else if (StringUtils.equals(esalias, EsAliasEnum.LONG.getEsAlias()) ||
                    StringUtils.equals(esalias, EsAliasEnum.DATE.getEsAlias())) {
                funName = XbbConstant.JSON_EXTRACT_BIGINT;
            } else if (StringUtils.equals(esalias, EsAliasEnum.ARRAY.getEsAlias())) {
                //todo 特殊类型用arrayrow
                funName = JSON_EXTRACT_RAW;
            } else if (Objects.equals(FieldTypeEnum.DEPT.getType(), fieldType)) {
                funName = XbbConstant.JSON_EXTRACT_BIGINT;
            }
        }
        return funName;
    }



    /**
     * @param businessType
     * @return void
     * @throws
     * @Description 是否需要关联子表单
     * @Date 10:55 2022/1/11
     **/

    private static boolean isNeedRelationSubFormTable(Integer businessType, String attr) {
        if (StringUtils.isEmpty(attr) || !attr.startsWith("subForm") || Objects.isNull(businessType)) {
            return false;
        }
        if (Objects.equals(XbbRefTypeEnum.CLUE.getCode(), businessType)
                || Objects.equals(XbbRefTypeEnum.WORK_ORDER.getCode(), businessType) || Objects.equals(XbbRefTypeEnum.SUPPLIER.getCode(), businessType) ||
                Objects.equals(XbbRefTypeEnum.SUPPLIER_CONTACT.getCode(), businessType)
                || Objects.equals(XbbRefTypeEnum.CONTACT.getCode(), businessType) || Objects.equals(XbbRefTypeEnum.CUSTOMER_MANAGEMENT.getCode(), businessType)) {
            if (StringUtils.equals("subForm_1", attr)) {
                return false;
            } else {
                return true;
            }
        }
        //subForm_2特殊处理
        if (StringUtils.equals("subForm_2", attr) && Objects.equals(XbbRefTypeEnum.WORK_REPORT.getCode(), businessType)) {
            return false;
        }
        //subForm_3特殊处理
        if (StringUtils.equals("subForm_3", attr) && Objects.equals(XbbRefTypeEnum.WORK_ORDER_OUTSTOCK.getCode(), businessType)) {
            return false;
        }
        return true;
    }

    /**
     * @return java.lang.String
     * @throws
     * @Description 根据函数名、属性名生成包装sql段
     * @Date 10:01 2021/12/14
     * @Param [attr, funName]
     **/

    private static String getStringWithFunN(String funName, String attr) {
        if (StringUtils.equals(JSON_EXTRACT,funName)) {
            attr = MessageFormat.format("{0}(`data`,''{1}'',''{2}'') ", funName, attr,STRING_CONSTANT);
        }else {
            attr = MessageFormat.format("{0}(`data`,''{1}'') ", funName, attr);
        }
        return attr;
    }

    private void handlerMainSubForm(SqlBuilderHandler sqlBuilder, String noEmptyStr, String notEqual, Object s2) {
        noEmptyStr = String.format(noEmptyStr, notEqual, s2);
        //stringBuffer.append(XbbConstant.AND).append(noEmptyStr);
        sqlBuilder.and(noEmptyStr);
        return;
    }

    /**
     * @param attr
     * @param sqlBuilder
     * @param operator
     * @param businessType
     * @return void
     * @throws
     * @Description 处理子表单空、非空
     * @Date 17:01 2022/1/11
     **/

    private static void handlerSubformComboEmpty(String attr, SqlBuilderHandler sqlBuilder, String operator, Integer businessType) {
        if (StringUtils.isEmpty(operator)) {
            return;
        }
        if (StringUtils.isNotEmpty(attr) && attr.contains(StringConstant.POINT)) {
            String[] attrArray = attr.split("\\.");
            String subForm = attrArray[0];
            if (StringUtils.isNotEmpty(subForm) && subForm.indexOf("_") != -1) {
                if (isNeedRelationSubFormTable(businessType, subForm)) {
                    String attrStr = " attr = '" + subForm + "' AND ";
                    String sql = sqlBuilder.end().getSql();
                    if (null != sql && sql.indexOf(attrStr) != -1) {
                        attrStr = "";
                    }
                    attr = attrStr + XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attrArray[1] + "')" + ") " + operator + " 0 ";
                    //stringBuffer.append(XbbConstant.AND).append(attr);
                    sqlBuilder.and(attr);
                } else {
                    //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "')" + ") " + operator + " 0 ");
                    sqlBuilder.and(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "')" + ") " + operator + " 0 ");
                }
            }
        } else {
            //stringBuffer.append(XbbConstant.AND).append(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "')" + ") " + operator + " 0 ");
            sqlBuilder.and(XbbConstant.LENGTH + "(" + JSON_EXTRACT_ARRAY_RAW + "(`data`,'" + attr + "')" + ") " + operator + " 0 ");
        }
    }

    public static String getNumExtractFuntion(Object value) {
        String funName = XbbConstant.JSON_EXTRACT_DOUBLE;
        if (value instanceof Integer) {
            funName = XbbConstant.JSON_EXTRACT_BIGINT;
        } /*else if (value instanceof String) {
                funName = XbbConstant.JSON_EXTRACT_STRING;
            }*/
        return funName;
    }

    /**
     * @author  gzt
     * @Description 根据JSON_EXTRACT函数类型确定拼接sql
     * @Date 10:23 2022/4/6
     * @return java.lang.String
     * @throws
     **/

    public static String getExtractString(String jsonExtract,String formId,String linkedText) {
        String extractString;
        if (Objects.isNull(formId)) {
            formId = "";
        }else {
            formId = String.format("%s%s", formId, XbbConstant.POINT);
        }
        if (StringUtils.equals(jsonExtract, XbbConstant.JSON_EXTRACT)) {
            //extractString = jsonExtract + "(" +formId+ "`data`,'" + linkedText + "','"+XbbConstant.STRING_CONSTANT+"') ";
            extractString = MessageFormat.format("{0}{1}{2}{3}{4}{5}{6}{7}{8}{9}{10}{11}{12}{13}", jsonExtract, XbbConstant.L_P, formId, XbbConstant.DATA, XbbConstant.SQL_COMMA, XbbConstant.SINGLE_QUOTES, linkedText, XbbConstant.SINGLE_QUOTES, XbbConstant.SQL_COMMA, XbbConstant.SINGLE_QUOTES, XbbConstant.STRING_CONSTANT, XbbConstant.SINGLE_QUOTES, XbbConstant.R_P, XbbConstant.SPACE);
        }else{
            //extractString = jsonExtract + "(" +formId+ "`data`,'" + linkedText + "') ";
            extractString = MessageFormat.format("{0}{1}{2}{3}{4}{5}{6}{7}{8}{9}", jsonExtract, XbbConstant.L_P, formId, XbbConstant.DATA, XbbConstant.SQL_COMMA, XbbConstant.SINGLE_QUOTES, linkedText, XbbConstant.SINGLE_QUOTES, XbbConstant.R_P, XbbConstant.SPACE);
        }
        return extractString;
    }
    private static void greaterSql(ConditionsEntityExt condition, String attr, Integer fieldType, SqlBuilderHandler sqlBuilder, String logicalSymbol, String corpid, Integer businessType) {
        Object value = condition.getValue().get(0);
        //子表单日期过滤需要特殊处理
        if ((Objects.equals(FieldTypeEnum.NUM.getType(), condition.getFieldType()) && RegexUtil.isNumber(value.toString())) || isNeedRelationSubFormTable(businessType, attr)) {
            /*if (value.toString().contains(".")) {
                value = Double.valueOf(value.toString());
                if (EsUtil.setInData(fieldType)) {
                    attr = XbbConstant.JSON_EXTRACT_DOUBLE + "(`data`,'" + attr + "')";
                }
            } else {
                value = Long.valueOf(value.toString());
                if (EsUtil.setInData(fieldType)) {
                    attr = XbbConstant.JSON_EXTRACT_DOUBLE + "(`data`,'" + attr + "')";
                }
            }*/
            if (BIHelp.setInData(fieldType)) {
                //attr = XbbConstant.JSON_EXTRACT_DOUBLE + "(`data`,'" + attr + "')";
                attr = getFunctionAttr(attr, fieldType, value, XbbConstant.JSON_EXTRACT_DOUBLE, businessType, sqlBuilder);
            }
            //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(logicalSymbol).append(value);
            sqlBuilder.and(attr,logicalSymbol,value);
        } else {//todo 非数字要抛异常
            long time = Long.parseLong(condition.getValue().get(0).toString());
            FieldTypeEnum dateFieldTypeEnum = FieldTypeEnum.getFieldTypeEnum(condition.getFieldType());
            if (Objects.nonNull(dateFieldTypeEnum) && Objects.equals(dateFieldTypeEnum.getEsalias(), FieldTypeEnum.DATETIME.getEsalias())) {
                time = DateTimeUtil.getTodayInt(time) + TimeConstant.SECONDS_PER_DAY - 1;
            }
            if (EsUtil.setInData(fieldType)) {
                attr = XbbConstant.JSON_EXTRACT_INT + "(`data`,'" + attr + "')";
            }
            //stringBuffer.append(XbbConstant.AND).append(attr).append(XbbConstant.SPACE).append(logicalSymbol).append(time);
            sqlBuilder.and(attr,logicalSymbol,time);
        }
    }
    /**
     * 生成初始的sql拼装语句
     *
     * @param corpid
     * @param formId
     * @param businessType
     * @param sqlBuilder
     */
    public SqlBuilderHandler initSqlString(String corpid, Long formId, Integer businessType,
                                           SqlBuilderHandler sqlBuilder, String attr) {
        if (sqlBuilder == null) {
            sqlBuilder = SqlBuilderHandler.start();
            XbbTableRefEnum xbbTableRefEnum = XbbTableRefEnum.getByCode(businessType);
            Long mainFormId = formId;
            PaasFormRefEntity paasFormRefEntity = null;
            if (BIProductReformEnum.getProductSubBusList().contains(businessType)) {
                paasFormRefEntity = paasFormRefModel.getMainFormIdByFormId(formId, corpid);
                if (!Objects.isNull(paasFormRefEntity)) {
                    mainFormId = paasFormRefEntity.getMainFormId();
                }
            }
            String tableName = xbbTableRefEnum.getTableName();
            sqlBuilder.from(tableName).where("corpid = '"+corpid+XbbConstant.SINGLE_QUOTES);
            //stringBuffer.append(" from ").append(tableName).append(" prewhere ").append("corpid = ").append("'").append(corpid).append("'").append(XbbConstant.SPACE);
            // 这些业务里没有formId，过滤掉
            boolean isRefund = Objects.equals(businessType, XbbRefTypeEnum.CONTRACT_PRODUCT.getCode()) && paasFormRefEntity != null && Objects.equals(paasFormRefEntity.getType(), XbbRefTypeEnum.REFUND.getCode());
            if (mainFormId != null) {
                if (!isRefund
                        && !Objects.equals(businessType, XbbRefTypeEnum.QUOTATION_PRODUCT.getCode())
                        && !Objects.equals(businessType, XbbRefTypeEnum.SUPPLIER_PRODUCT.getCode())
                        && !Objects.equals(businessType, XbbRefTypeEnum.PURCHASE_PRODUCT.getCode())
                        && !Objects.equals(businessType, XbbRefTypeEnum.RETURNEDPURCHASE_PRODUCT.getCode())
                        && !Objects.equals(businessType, XbbRefTypeEnum.BOM_BILL_PRODUCT.getCode())
                        && !Objects.equals(businessType, XbbRefTypeEnum.PRODUCTION_ORDER_PRODUCT.getCode())
                        && !Objects.equals(businessType, XbbRefTypeEnum.ASSEMBLE_PRODUCT.getCode())
                        && !Objects.equals(businessType, XbbRefTypeEnum.INVENTORY_PRODUCT.getCode())
                        && !Objects.equals(businessType, XbbRefTypeEnum.CONTRACT_PERFORMANCE.getCode())) {
                    //stringBuffer.append(XbbConstant.AND).append(" form_id = ").append(mainFormId).append(XbbConstant.SPACE);
                    sqlBuilder.and(" form_id = ",mainFormId);
                } else if (Objects.equals(businessType, XbbRefTypeEnum.PRODUCTION_ORDER_PRODUCT.getCode())
                        || Objects.equals(businessType, XbbRefTypeEnum.ASSEMBLE_PRODUCT.getCode())
                        || Objects.equals(businessType, XbbRefTypeEnum.BOM_BILL_PRODUCT.getCode())
                        || Objects.equals(businessType, XbbRefTypeEnum.CONTRACT_PRODUCT.getCode())
                        || Objects.equals(businessType, XbbRefTypeEnum.INSTOCK_PRODUCT.getCode())
                        || Objects.equals(businessType, XbbRefTypeEnum.OUTSTOCK_PRODUCT.getCode())
                ) {
                    if (Objects.nonNull(paasFormRefEntity) && Objects.nonNull(paasFormRefEntity.getType())){
                        //stringBuffer.append(XbbConstant.AND).append(" type = ").append(paasFormRefEntity.getType()).append(XbbConstant.SPACE);
                        sqlBuilder.and(" type = ",paasFormRefEntity.getType());
                    }
                }
            }
            // 退货退款产品和合同产品存一起，但form_id只存合同的，很迷，目前退货退款是单模板，用type做区分了，若后期开多模板，自求多福吧
            if (paasFormRefEntity != null
                    && (isRefund || Objects.equals(businessType, XbbRefTypeEnum.ASSEMBLE_PRODUCT.getCode()) || Objects.equals(businessType, XbbRefTypeEnum.BOM_BILL_PRODUCT.getCode()))) {
                //stringBuffer.append(XbbConstant.AND).append(" type = ").append(paasFormRefEntity.getType()).append(XbbConstant.SPACE);
                sqlBuilder.and(" type = ",paasFormRefEntity.getType());
            }
            // 审批流程默认值
            handleFlowStatus(businessType, sqlBuilder);
            sqlBuilder.append(XbbConstant.GROUP_ID).append(" having del = 0 ");
            //sqlBuilder.append(XbbConstant.GROUP_ID);
            //filterMap.put(formId.toString(), stringBuffer);
        } else {
            if (StringUtils.isNotEmpty(attr) && attr.contains(StringConstant.POINT) && !Objects.isNull(businessType)) {
                String subFormTableName = subFormHashMap.get(businessType);
                if (StringUtils.isEmpty(subFormTableName)) {
                    subFormTableName = XbbTableRefEnum.getSubFormNameByParent(businessType);
                    subFormHashMap.putIfAbsent(businessType, subFormTableName);
                }
                if (StringUtils.isNotEmpty(subFormTableName)) {
                    String subFormIn = "SELECT data_id from " + subFormTableName + " prewhere corpid = '" + corpid + "' and del =0 ";
                    String[] attrArray = attr.split("\\.");
                    String subForm = attrArray[0];
                    if (StringUtil.isEmpty(subForm)) {
                        return sqlBuilder;
                    }
                    int indexOf = sqlBuilder.end().getSql().indexOf(subFormTableName);
                    boolean needRelationSubFormTable = isNeedRelationSubFormTable(businessType, subForm);
                    if (indexOf == -1) {
                        currentSubFormName = subForm;
                        if (needRelationSubFormTable) {
                            String subFormSql = " AND id in (SELECT data_id from " + subFormTableName + "  where corpid ='" + corpid + "' " +
                                    " and del =0 ";
                            sqlBuilder.append(subFormSql);
                        }
                    }
                    if (indexOf != -1) {
                        if (needRelationSubFormTable) {
                            if (!StringUtils.equals(currentSubFormName, subForm)) {
                                currentSubFormName = subForm;
                                //阿里云版ck不支持intersect，用in代替
                                //stringBuffer.append(" intersect ").append(subFormIn);
                                sqlBuilder.append(" and  data_id in ( ").append(subFormIn);
                            }
                        }
                    }
                }
            }
        }
        return sqlBuilder;
    }


    /**
     * 审批默认条件
     *
     * @param businessType
     * @param sqlBuilder
     * @author xinpeng.jiang
     * @date 2022/2/15 19:14
     * @version 1.0
     */
    private static void  handleFlowStatus(Integer businessType, SqlBuilderHandler sqlBuilder) {
        if (!Objects.equals(businessType, XbbRefTypeEnum.CONTRACT_PERFORMANCE.getCode())
                && !Objects.equals(businessType, XbbRefTypeEnum.CONTRACT_PRODUCT.getCode())
                && !Objects.equals(businessType, XbbRefTypeEnum.SALES_OPPORTUNITY_PRODUCT.getCode())
                && !Objects.equals(businessType, XbbRefTypeEnum.QUOTATION_PRODUCT.getCode())
                && !Objects.equals(businessType, XbbRefTypeEnum.SUPPLIER_PRODUCT.getCode())
                && !Objects.equals(businessType, XbbRefTypeEnum.PURCHASE_PRODUCT.getCode())
                && !Objects.equals(businessType, XbbRefTypeEnum.RETURNEDPURCHASE_PRODUCT.getCode())
                && !Objects.equals(businessType, XbbRefTypeEnum.BOM_BILL_PRODUCT.getCode())
                && !Objects.equals(businessType, XbbRefTypeEnum.PRODUCTION_ORDER_PRODUCT.getCode())
                && !Objects.equals(businessType, XbbRefTypeEnum.ASSEMBLE_PRODUCT.getCode())
                && !Objects.equals(businessType, XbbRefTypeEnum.INVENTORY_PRODUCT.getCode())
                && !Objects.equals(businessType, XbbRefTypeEnum.TRANSFER_PRODUCT.getCode())
                && !Objects.equals(businessType, XbbRefTypeEnum.INSTOCK_PRODUCT.getCode())
                && !Objects.equals(businessType, XbbRefTypeEnum.OUTSTOCK_PRODUCT.getCode())
        ) {
            String flowStatusAttr = StringUtil.getDataBaseString(BiEsAliasEnum.FLOWSTATUS.getEsAlias());
            /*stringBuffer.append(XbbConstant.SPACE)
                    .append(XbbConstant.AND).append(XbbConstant.SPACE)
                    .append(flowStatusAttr)
                    .append(XbbConstant.SPACE)
                    .append(ConditionEnum.IN.getReplaceSymbol()).append(XbbConstant.SPACE)
                    .append(Stream.of(FlowStatusEnum.NOT_NEED_APPROVAL.getType(), FlowStatusEnum.PASS.getType()).collect(Collectors.toList()));*/
            sqlBuilder.and(flowStatusAttr,ConditionEnum.IN.getReplaceSymbol(),Stream.of(FlowStatusEnum.NOT_NEED_APPROVAL.getType(), FlowStatusEnum.PASS.getType()).collect(Collectors.toList()));
        }
    }


    public static void subFormParseValue(ConditionsEntityExt condition) {
        List<Object> values = condition.getValue();
        List<Object> list = new ArrayList<>();
        Integer fieldType = condition.getFieldType();
        FieldTypeEnum fieldTypeEnum = FieldTypeEnum.getFieldTypeEnum(fieldType);
        if (condition.getPenetrateFlag() != null && Objects.equals(1, condition.getPenetrateFlag())) {
            Object value;
            if (CollectionsUtil.isEmpty(values)) {
                value = "";
                list.add(value);
                condition.setValue(list);
            }
        } else {
            if (fieldTypeEnum != null) {
                switch (fieldTypeEnum) {
                    case USER:
                    case CREATORID:
                    case OWNERID:
                    case COUSERID:
                    case USER_GROUP:
                        if (CollectionsUtil.isNotEmpty(values)) {
                            for (Object obj : values) {
                                if (obj instanceof String) {
                                    list.add(obj);
                                } else if (obj instanceof JSONObject) {
                                    JSONObject jsonObject = (JSONObject) obj;
                                    String userId = jsonObject.getString(ParameterConstant.USER_ID);
                                    list.add(userId);
                                }
                            }
                        }
                        condition.setValue(list);
                        break;
                    case DEPT:
                    case DEPT_GROUP:
                    case DEPARTMENTID:
                        if (CollectionsUtil.isNotEmpty(values)) {
                            for (Object obj : values) {
                                if (obj instanceof String || obj instanceof Integer || obj instanceof Long) {
                                    list.add(obj);
                                } else if (obj instanceof JSONObject) {
                                    JSONObject jsonObject = (JSONObject) obj;
                                    String userId = jsonObject.getString("id");
                                    list.add(userId);
                                }
                            }
                        }
                        condition.setValue(list);
                        break;
                    case CATEGORY_ID:
                        if (CollectionsUtil.isNotEmpty(values)) {
                            for (Object obj : values) {
                                if (obj instanceof Integer) {
                                    list.add(obj);
                                } else if (obj instanceof JSONObject) {
                                    JSONObject jsonObject = (JSONObject) obj;
                                    Integer categoryId = jsonObject.getInteger("id");
                                    list.add(categoryId);
                                }
                            }
                        }
                        condition.setValue(list);
                        break;
                    case CONTACT_NUMBER:
                        condition.setAttr(condition.getAttr() + StringConstant.POINT + StringConstant.PHONE_TEL_ATTR);
                        break;
                    case ADDRESS:
                        //地址特殊处理
                        String attr;
                        String[] attrs = condition.getAttr().split("\\.");
                        if (attrs.length > 0) {
                            attr = attrs[attrs.length - 1];
                            if (AddressEnum.getByAlias(attr) != null) {
                                Object value = null;
                                if (AddressEnum.CITY.equals(AddressEnum.getByAlias(attr))) {
                                    value = condition.getValue().get(1);
                                } else if (AddressEnum.DISTRICT.equals(AddressEnum.getByAlias(attr))) {
                                    value = condition.getValue().get(2);
                                }
                                if (value != null) {
                                    List valueList = new ArrayList();
                                    valueList.add(value);
                                    condition.setValue(valueList);
                                }
                            }
                        }
                        break;
                    default:
                        break;
                }
            }
        }
    }

    /**
     * 查询时默认的一些规则条件组装
     *
     * @param corpid
     * @param formId
     * @param businessType
     * @param formIdAttr   多表对应的attr
     * @throws XbbException
     * @author xinpeng.jiang
     * @date 2021/12/22 10:18
     * @version 1.0
     */
    public void addDefaultFilter(String corpid, Long formId, Integer businessType, JSONObject formIdAttr) throws XbbException {
        StringBuffer stringBuffer = new StringBuffer();
        XbbRefTypeEnum xbbRefTypeEnum = XbbRefTypeEnum.getByCode(businessType);
        String data = XbbConstant.DATA + SymbolConstant.COMMA_ENGLISH;
        switch (xbbRefTypeEnum) {
            case CONTRACT:
                //红冲合同不展示
                String attr = StringHelper.formatSingleQuotes4Attr(ContractEnum.IS_RED.getAttr());
                stringBuffer.append(XbbConstant.SPACE)
                        .append(XbbConstant.AND).append(XbbConstant.SPACE)
                        .append(XbbConstant.JSON_EXTRACT_INT).append(XbbConstant.L_P).append(data).append(attr).append(XbbConstant.R_P).append(XbbConstant.SPACE)
                        .append(ConditionEnum.EQUAL.getReplaceSymbol()).append(XbbConstant.SPACE)
                        .append(BasicConstant.ZERO);
                break;
            case PRODUCT:
                //多表时不考虑
                if (formIdAttr == null || formIdAttr.size() == 0) {
                    //只查子产品
                    String s = StringHelper.formatSingleQuotes4Attr(ProductEnum.PARENT_ID.getAttr());
                    stringBuffer.append(XbbConstant.SPACE)
                            .append(XbbConstant.AND).append(XbbConstant.SPACE)
                            .append(XbbConstant.JSON_EXTRACT_INT).append(XbbConstant.L_P).append(data).append(s).append(XbbConstant.R_P).append(XbbConstant.SPACE)
                            .append(ConditionEnum.NOEQUAL.getReplaceSymbol()).append(XbbConstant.SPACE)
                            .append(BasicConstant.ZERO);
                }
                break;
            case WORK_ORDER:
                //多表展示不考虑
                if (formIdAttr == null || formIdAttr.size() == 0) {
                    stringBuffer.append(XbbConstant.SPACE)
                            .append(XbbConstant.AND).append(XbbConstant.SPACE)
                            .append(FieldTypeEnum.WORK_ORDER_ROB.getAlias()).append(XbbConstant.SPACE)
                            .append(ConditionEnum.EQUAL.getReplaceSymbol()).append(XbbConstant.SPACE)
                            .append(WorkOrderTypeEnum.NORMAL.getCode());
                }
                break;
            case CUSTOMER_COMMUNICATE:
                //跟进记录多表已经在数据端处理
                //过滤项目日志
                String autoGen = StringHelper.formatSingleQuotes4Attr(CustomerCommunicateEnum.AUTO_GEN.getAttr());
                stringBuffer.append(XbbConstant.SPACE)
                        .append(XbbConstant.AND).append(XbbConstant.SPACE)
                        .append(XbbConstant.JSON_EXTRACT_INT).append(XbbConstant.L_P).append(data).append(autoGen).append(XbbConstant.R_P).append(XbbConstant.SPACE)
                        .append(ConditionEnum.EQUAL.getReplaceSymbol()).append(XbbConstant.SPACE)
                        .append(BasicConstant.ZERO);
                break;
            case PAYMENT_SHEET:
            case WRITE_OFF_PREPAYMENT:
            case RED_PAYMENT_SHEET:
            case RED_PREPAYMENT:
            case RED_WRITE_OFF_PREPAYMENT:
            case RED_WRITE_OFF_BALANCE:
            case PREPAYMENT:
            case BAD_DEBT:
            case PAY_SHEET:
            case WRITE_OFF_PREPAY:
            case RED_WRITE_OFF_PAY:
            case RED_PREPAY:
            case RED_WRITE_OFF_PREPAY:
            case PREPAY:
            case BAD_PAY:
                List<Integer> sheetTypeList;
                if (formIdAttr != null && formIdAttr.size() > 0) {
                    sheetTypeList = SheetAloneEnum.getNotParentCodeList();
                } else {
                    sheetTypeList = SheetAloneEnum.getNotChildCodeList();
                }
                stringBuffer.append(XbbConstant.SPACE)
                        .append(XbbConstant.AND).append(XbbConstant.SPACE)
                        .append(FieldTypeEnum.SHEET_ALONE.getAlias()).append(XbbConstant.SPACE);
                stringBuffer.append(ConditionEnum.IN.getReplaceSymbol()).append(XbbConstant.SPACE)
                        .append(sheetTypeList);

                break;
            default:
                break;
        }
        // 塞回去，没有初始化一个
        //filterMap.computeIfAbsent(String.valueOf(formId), v -> initSqlString(corpid, formId, businessType, filterMap, null, null)).append(stringBuffer);
    }
}
